*===============================================================================
*
*					WORKER BELIEFS ABOUT OUTSIDE OPTIONS
*		(c)	Simon Jaeger, Christopher Roth, Nina Roussille, Benjamin Schoefer
*							  2023 December 5
*						   	   SOEP-IAB Data 
*
*===============================================================================


********************************************************************************
*			Cell Level Wage Calculations for Information Experiment	 		   *
********************************************************************************


clear
cap log close                                             
log using ${log}/Cell_calculations_exp.log, replace



use prs_id betnr ieb_beg_epi ieb_end_epi ieb_tag_entg sex_id ieb_beruf_kons_num b_gem_schl_ao_akt_num ieb_dba_id geb_dat berufstellg_imp ieb_dba_id ///
	if year(ieb_beg_epi)==2019 | year(ieb_beg_epi)==2017 using "$orig/coworker_fullhist_IEB.dta", clear
	
gen jahr = year(ieb_beg_epi)

keep if betnr!=.  // only employment spells

* one main spell (with highest compensation)
gen spell_earnings = (ieb_end_epi-ieb_beg_epi+1)*ieb_tag_entg
drop if spell_earnings == . | spell_earnings<0
gen sort_spell_earnings = -spell_earnings
bys prs_id jahr (sort_spell_earnings): gen priority_spell = _n
keep if priority_spell==1
drop spell_earnings priority_spell

* fulltime
tab berufstellg_imp, m
replace berufstellg_imp = . if berufstellg_imp<0 
gen fulltime = !(inlist(berufstellg_imp,8,9,21))
replace fulltime = . if berufstellg_imp==.
tab fulltime, m
keep if fulltime==1

* sex
rename sex_id female
tab female, m

* age
gen yob = year(geb_dat)
gen age = jahr-yob
tab age, m // checking
gen age2 = age^2
gen age3 = age^3

* occupation
su ieb_beruf_kons_num, d // checking the variable
rename ieb_beruf_kons_num occupation
tab occupation, m // checking
replace occupation = . if occupation<0 // dealing with missings that have negative values
cap noisily tab occupation, m

* education
rename ieb_dba_id ausbildung
tab ausbildung // checking
gen educ = .
replace educ = 1 if ausbildung==1 // " Ohne abgeschlossene Berufsausbildung"
replace educ = 2 if ausbildung==2 // "Betriebl./ausserbetriebliche Ausbildu"
replace educ = 3 if ausbildung==11 | ausbildung==12 // "Hochschulabschluss ohne naehere Spez"
tab educ, m

* inflation-adjusting 2017 wages
replace ieb_tag_entg = ieb_tag_entg*1.031*1.026 if jahr==2017

* log wage
* winsorizing separately by year
su ieb_tag_entg, d // diagnostic
gen ln_wage_non_winsorized19 = ln(ieb_tag_entg) if jahr==2019 
gen ln_wage_non_winsorized17 = ln(ieb_tag_entg) if jahr==2017 
cap noisily winsor ln_wage_non_winsorized19, p(0.02) generate(ln_wage19) 
cap noisily winsor ln_wage_non_winsorized17, p(0.02) generate(ln_wage17)
drop ln_wage_non_winsorized*
gen ln_wage = .
replace ln_wage = ln_wage19 if jahr==2019
replace ln_wage = ln_wage17 if jahr==2017
drop ln_wage19 ln_wage17


* region
replace b_gem_schl_ao_akt_num = . if b_gem_schl_ao_akt_num<0 // places outside Germany
rename b_gem_schl_ao_akt_num ao_kreis
if 1==1 { // crosswalking to lm_region

gen int lm_region = . 
label variable lm_region "Kropp/Schwengler Region"

//transform ao_kreis from gemeinde-level to kreis-level info
replace ao_kreis = floor(ao_kreis/1000)

// Perform crosswalk
replace lm_region = 	2000000	if ao_kreis==	1001
replace lm_region = 	2000000	if ao_kreis==	1002
replace lm_region = 	2000000	if ao_kreis==	1003
replace lm_region = 	2000000	if ao_kreis==	1004
replace lm_region = 	2000000	if ao_kreis==	1051
replace lm_region = 	2000000	if ao_kreis==	1053
replace lm_region = 	2000000	if ao_kreis==	1054
replace lm_region = 	2000000	if ao_kreis==	1055
replace lm_region = 	2000000	if ao_kreis==	1056
replace lm_region = 	2000000	if ao_kreis==	1057
replace lm_region = 	2000000	if ao_kreis==	1058
replace lm_region = 	2000000	if ao_kreis==	1059
replace lm_region = 	2000000	if ao_kreis==	1060
replace lm_region = 	2000000	if ao_kreis==	1061
replace lm_region = 	2000000	if ao_kreis==	1062
replace lm_region = 	2000000	if ao_kreis==	2000
replace lm_region = 	3101000	if ao_kreis==	3101
replace lm_region = 	3101000	if ao_kreis==	3102
replace lm_region = 	3101000	if ao_kreis==	3103
replace lm_region = 	3101000	if ao_kreis==	3151
replace lm_region = 	3152012	if ao_kreis==	3152
replace lm_region = 	3101000	if ao_kreis==	3153
replace lm_region = 	3101000	if ao_kreis==	3154
replace lm_region = 	3152012	if ao_kreis==	3155
replace lm_region = 	3152012	if ao_kreis==	3156
replace lm_region = 	3101000	if ao_kreis==	3157
replace lm_region = 	3101000	if ao_kreis==	3158
replace lm_region = 	3241001	if ao_kreis==	3241
replace lm_region = 	4011000	if ao_kreis==	3251
replace lm_region = 	3241001	if ao_kreis==	3252
replace lm_region = 	3241001	if ao_kreis==	3254
replace lm_region = 	5711000	if ao_kreis==	3255
replace lm_region = 	3241001	if ao_kreis==	3256
replace lm_region = 	3241001	if ao_kreis==	3257
replace lm_region = 	3241001	if ao_kreis==	3351
replace lm_region = 	4011000	if ao_kreis==	3352
replace lm_region = 	2000000	if ao_kreis==	3353
replace lm_region = 	2000000	if ao_kreis==	3354
replace lm_region = 	2000000	if ao_kreis==	3355
replace lm_region = 	4011000	if ao_kreis==	3356
replace lm_region = 	4011000	if ao_kreis==	3357
replace lm_region = 	3241001	if ao_kreis==	3358
replace lm_region = 	2000000	if ao_kreis==	3359
replace lm_region = 	2000000	if ao_kreis==	3360
replace lm_region = 	4011000	if ao_kreis==	3361
replace lm_region = 	4011000	if ao_kreis==	3401
replace lm_region = 	3403000	if ao_kreis==	3402
replace lm_region = 	3403000	if ao_kreis==	3403
replace lm_region = 	3404000	if ao_kreis==	3404
replace lm_region = 	3403000	if ao_kreis==	3405
replace lm_region = 	3403000	if ao_kreis==	3451
replace lm_region = 	3403000	if ao_kreis==	3452
replace lm_region = 	3403000	if ao_kreis==	3453
replace lm_region = 	5515000	if ao_kreis==	3454
replace lm_region = 	3403000	if ao_kreis==	3455
replace lm_region = 	5515000	if ao_kreis==	3456
replace lm_region = 	3403000	if ao_kreis==	3457
replace lm_region = 	4011000	if ao_kreis==	3458
replace lm_region = 	3404000	if ao_kreis==	3459
replace lm_region = 	3404000	if ao_kreis==	3460
replace lm_region = 	3403000	if ao_kreis==	3461
replace lm_region = 	3403000	if ao_kreis==	3462
replace lm_region = 	4011000	if ao_kreis==	4011
replace lm_region = 	4011000	if ao_kreis==	4012
replace lm_region = 	5113000	if ao_kreis==	5111
replace lm_region = 	5113000	if ao_kreis==	5112
replace lm_region = 	5113000	if ao_kreis==	5113
replace lm_region = 	5113000	if ao_kreis==	5114
replace lm_region = 	5113000	if ao_kreis==	5116
replace lm_region = 	5113000	if ao_kreis==	5117
replace lm_region = 	5113000	if ao_kreis==	5119
replace lm_region = 	5113000	if ao_kreis==	5120
replace lm_region = 	5113000	if ao_kreis==	5122
replace lm_region = 	5113000	if ao_kreis==	5124
replace lm_region = 	5113000	if ao_kreis==	5154
replace lm_region = 	5113000	if ao_kreis==	5158
replace lm_region = 	5113000	if ao_kreis==	5162
replace lm_region = 	5113000	if ao_kreis==	5166
replace lm_region = 	5113000	if ao_kreis==	5170
replace lm_region = 	5313000	if ao_kreis==	5313
replace lm_region = 	5315000	if ao_kreis==	5314
replace lm_region = 	5315000	if ao_kreis==	5315
replace lm_region = 	5315000	if ao_kreis==	5316
replace lm_region = 	5313000	if ao_kreis==	5354
replace lm_region = 	5313000	if ao_kreis==	5334
replace lm_region = 	5313000	if ao_kreis==	5358
replace lm_region = 	5315000	if ao_kreis==	5362
replace lm_region = 	5315000	if ao_kreis==	5366
replace lm_region = 	5113000	if ao_kreis==	5370
replace lm_region = 	5315000	if ao_kreis==	5374
replace lm_region = 	5315000	if ao_kreis==	5378
replace lm_region = 	5315000	if ao_kreis==	5382
replace lm_region = 	5113000	if ao_kreis==	5512
replace lm_region = 	5113000	if ao_kreis==	5513
replace lm_region = 	5515000	if ao_kreis==	5515
replace lm_region = 	5515000	if ao_kreis==	5554
replace lm_region = 	5515000	if ao_kreis==	5558
replace lm_region = 	5113000	if ao_kreis==	5562
replace lm_region = 	5515000	if ao_kreis==	5566
replace lm_region = 	5113000	if ao_kreis==	5570
replace lm_region = 	5711000	if ao_kreis==	5711
replace lm_region = 	5711000	if ao_kreis==	5754
replace lm_region = 	5711000	if ao_kreis==	5758
replace lm_region = 	5711000	if ao_kreis==	5762
replace lm_region = 	5711000	if ao_kreis==	5766
replace lm_region = 	5711000	if ao_kreis==	5770
replace lm_region = 	5711000	if ao_kreis==	5774
replace lm_region = 	5113000	if ao_kreis==	5911
replace lm_region = 	5113000	if ao_kreis==	5913
replace lm_region = 	5113000	if ao_kreis==	5914
replace lm_region = 	5113000	if ao_kreis==	5915
replace lm_region = 	5113000	if ao_kreis==	5916
replace lm_region = 	5113000	if ao_kreis==	5954
replace lm_region = 	5113000	if ao_kreis==	5958
replace lm_region = 	5113000	if ao_kreis==	5962
replace lm_region = 	5970040	if ao_kreis==	5966
replace lm_region = 	5970040	if ao_kreis==	5970
replace lm_region = 	5711000	if ao_kreis==	5974
replace lm_region = 	5113000	if ao_kreis==	5978
replace lm_region = 	6412000	if ao_kreis==	6411
replace lm_region = 	6412000	if ao_kreis==	6412
replace lm_region = 	6412000	if ao_kreis==	6413
replace lm_region = 	6412000	if ao_kreis==	6414
replace lm_region = 	8222000	if ao_kreis==	6431
replace lm_region = 	6412000	if ao_kreis==	6432
replace lm_region = 	6412000	if ao_kreis==	6433
replace lm_region = 	6412000	if ao_kreis==	6434
replace lm_region = 	6412000	if ao_kreis==	6435
replace lm_region = 	6412000	if ao_kreis==	6436
replace lm_region = 	6412000	if ao_kreis==	6437
replace lm_region = 	6412000	if ao_kreis==	6438
replace lm_region = 	6412000	if ao_kreis==	6439
replace lm_region = 	6412000	if ao_kreis==	6440
replace lm_region = 	6412000	if ao_kreis==	6531
replace lm_region = 	6412000	if ao_kreis==	6532
replace lm_region = 	6412000	if ao_kreis==	6533
replace lm_region = 	6412000	if ao_kreis==	6534
replace lm_region = 	6412000	if ao_kreis==	6535
replace lm_region = 	6611000	if ao_kreis==	6611
replace lm_region = 	6412000	if ao_kreis==	6631
replace lm_region = 	6611000	if ao_kreis==	6632
replace lm_region = 	6611000	if ao_kreis==	6633
replace lm_region = 	6611000	if ao_kreis==	6634
replace lm_region = 	6611000	if ao_kreis==	6635
replace lm_region = 	6611000	if ao_kreis==	6636
replace lm_region = 	7111000	if ao_kreis==	7111
replace lm_region = 	5315000	if ao_kreis==	7131
replace lm_region = 	5970040	if ao_kreis==	7132
replace lm_region = 	6412000	if ao_kreis==	7133
replace lm_region = 	6412000	if ao_kreis==	7134
replace lm_region = 	7111000	if ao_kreis==	7135
replace lm_region = 	7111000	if ao_kreis==	7137
replace lm_region = 	7111000	if ao_kreis==	7138
replace lm_region = 	7111000	if ao_kreis==	7140
replace lm_region = 	6412000	if ao_kreis==	7141
replace lm_region = 	7111000	if ao_kreis==	7143
replace lm_region = 	7211000	if ao_kreis==	7211
replace lm_region = 	7211000	if ao_kreis==	7231
replace lm_region = 	7211000	if ao_kreis==	7232
replace lm_region = 	7211000	if ao_kreis==	7233
replace lm_region = 	7211000	if ao_kreis==	7235
replace lm_region = 	8222000	if ao_kreis==	7311
replace lm_region = 	10041100	if ao_kreis==	7312
replace lm_region = 	8222000	if ao_kreis==	7313
replace lm_region = 	8222000	if ao_kreis==	7314
replace lm_region = 	6412000	if ao_kreis==	7315
replace lm_region = 	8222000	if ao_kreis==	7316
replace lm_region = 	10041100	if ao_kreis==	7317
replace lm_region = 	8222000	if ao_kreis==	7318
replace lm_region = 	8222000	if ao_kreis==	7319
replace lm_region = 	10041100	if ao_kreis==	7320
replace lm_region = 	6412000	if ao_kreis==	7331
replace lm_region = 	8222000	if ao_kreis==	7332
replace lm_region = 	8222000	if ao_kreis==	7333
replace lm_region = 	8212000	if ao_kreis==	7334
replace lm_region = 	10041100	if ao_kreis==	7335
replace lm_region = 	10041100	if ao_kreis==	7336
replace lm_region = 	8222000	if ao_kreis==	7337
replace lm_region = 	8222000	if ao_kreis==	7338
replace lm_region = 	6412000	if ao_kreis==	7339
replace lm_region = 	10041100	if ao_kreis==	7340
replace lm_region = 	8111000	if ao_kreis==	8111
replace lm_region = 	8111000	if ao_kreis==	8115
replace lm_region = 	8111000	if ao_kreis==	8116
replace lm_region = 	8111000	if ao_kreis==	8117
replace lm_region = 	8111000	if ao_kreis==	8118
replace lm_region = 	8111000	if ao_kreis==	8119
replace lm_region = 	8111000	if ao_kreis==	8121
replace lm_region = 	8111000	if ao_kreis==	8125
replace lm_region = 	8111000	if ao_kreis==	8126
replace lm_region = 	8111000	if ao_kreis==	8127
replace lm_region = 	8111000	if ao_kreis==	8128
replace lm_region = 	8111000	if ao_kreis==	8135
replace lm_region = 	8111000	if ao_kreis==	8136
replace lm_region = 	8212000	if ao_kreis==	8211
replace lm_region = 	8212000	if ao_kreis==	8212
replace lm_region = 	8212000	if ao_kreis==	8215
replace lm_region = 	8212000	if ao_kreis==	8216
replace lm_region = 	8222000	if ao_kreis==	8221
replace lm_region = 	8222000	if ao_kreis==	8222
replace lm_region = 	8111000	if ao_kreis==	8225
replace lm_region = 	8222000	if ao_kreis==	8226
replace lm_region = 	8111000	if ao_kreis==	8231
replace lm_region = 	8111000	if ao_kreis==	8235
replace lm_region = 	8111000	if ao_kreis==	8236
replace lm_region = 	8111000	if ao_kreis==	8237
replace lm_region = 	8311000	if ao_kreis==	8311
replace lm_region = 	8311000	if ao_kreis==	8315
replace lm_region = 	8311000	if ao_kreis==	8316
replace lm_region = 	8317096	if ao_kreis==	8317
replace lm_region = 	8326074	if ao_kreis==	8325
replace lm_region = 	8326074	if ao_kreis==	8326
replace lm_region = 	8326074	if ao_kreis==	8327
replace lm_region = 	8335075	if ao_kreis==	8335
replace lm_region = 	8336050	if ao_kreis==	8336
replace lm_region = 	8336050	if ao_kreis==	8337
replace lm_region = 	8111000	if ao_kreis==	8415
replace lm_region = 	8111000	if ao_kreis==	8416
replace lm_region = 	8111000	if ao_kreis==	8417
replace lm_region = 	8421000	if ao_kreis==	8421
replace lm_region = 	8421000	if ao_kreis==	8425
replace lm_region = 	8421000	if ao_kreis==	8426
replace lm_region = 	8436064	if ao_kreis==	8435
replace lm_region = 	8436064	if ao_kreis==	8436
replace lm_region = 	8421000	if ao_kreis==	8437
replace lm_region = 	9162000	if ao_kreis==	9161
replace lm_region = 	9162000	if ao_kreis==	9162
replace lm_region = 	9162000	if ao_kreis==	9163
replace lm_region = 	9162000	if ao_kreis==	9171
replace lm_region = 	9162000	if ao_kreis==	9172
replace lm_region = 	9162000	if ao_kreis==	9173
replace lm_region = 	9162000	if ao_kreis==	9174
replace lm_region = 	9162000	if ao_kreis==	9175
replace lm_region = 	9162000	if ao_kreis==	9176
replace lm_region = 	9162000	if ao_kreis==	9177
replace lm_region = 	9162000	if ao_kreis==	9178
replace lm_region = 	9162000	if ao_kreis==	9179
replace lm_region = 	9162000	if ao_kreis==	9180
replace lm_region = 	9162000	if ao_kreis==	9181
replace lm_region = 	9162000	if ao_kreis==	9182
replace lm_region = 	9162000	if ao_kreis==	9183
replace lm_region = 	9162000	if ao_kreis==	9184
replace lm_region = 	9162000	if ao_kreis==	9185
replace lm_region = 	9162000	if ao_kreis==	9186
replace lm_region = 	9162000	if ao_kreis==	9187
replace lm_region = 	9162000	if ao_kreis==	9188
replace lm_region = 	9162000	if ao_kreis==	9189
replace lm_region = 	9162000	if ao_kreis==	9190
replace lm_region = 	9162000	if ao_kreis==	9261
replace lm_region = 	9262000	if ao_kreis==	9262
replace lm_region = 	9362000	if ao_kreis==	9263
replace lm_region = 	9262000	if ao_kreis==	9271
replace lm_region = 	9262000	if ao_kreis==	9272
replace lm_region = 	9362000	if ao_kreis==	9273
replace lm_region = 	9162000	if ao_kreis==	9274
replace lm_region = 	9262000	if ao_kreis==	9275
replace lm_region = 	9262000	if ao_kreis==	9276
replace lm_region = 	9162000	if ao_kreis==	9277
replace lm_region = 	9362000	if ao_kreis==	9278
replace lm_region = 	9162000	if ao_kreis==	9279
replace lm_region = 	9564000	if ao_kreis==	9361
replace lm_region = 	9362000	if ao_kreis==	9362
replace lm_region = 	9363000	if ao_kreis==	9363
replace lm_region = 	9564000	if ao_kreis==	9371
replace lm_region = 	9362000	if ao_kreis==	9372
replace lm_region = 	9564000	if ao_kreis==	9373
replace lm_region = 	9363000	if ao_kreis==	9374
replace lm_region = 	9362000	if ao_kreis==	9375
replace lm_region = 	9362000	if ao_kreis==	9376
replace lm_region = 	9479136	if ao_kreis==	9377
replace lm_region = 	9564000	if ao_kreis==	9461
replace lm_region = 	9462000	if ao_kreis==	9462
replace lm_region = 	9463000	if ao_kreis==	9463
replace lm_region = 	9464000	if ao_kreis==	9464
replace lm_region = 	9564000	if ao_kreis==	9471
replace lm_region = 	9462000	if ao_kreis==	9472
replace lm_region = 	9463000	if ao_kreis==	9473
replace lm_region = 	9564000	if ao_kreis==	9474
replace lm_region = 	9464000	if ao_kreis==	9475
replace lm_region = 	9463000	if ao_kreis==	9476
replace lm_region = 	9462000	if ao_kreis==	9477
replace lm_region = 	9463000	if ao_kreis==	9478
replace lm_region = 	9479136	if ao_kreis==	9479
replace lm_region = 	9564000	if ao_kreis==	9561
replace lm_region = 	9564000	if ao_kreis==	9562
replace lm_region = 	9564000	if ao_kreis==	9563
replace lm_region = 	9564000	if ao_kreis==	9564
replace lm_region = 	9564000	if ao_kreis==	9565
replace lm_region = 	9564000	if ao_kreis==	9571
replace lm_region = 	9564000	if ao_kreis==	9572
replace lm_region = 	9564000	if ao_kreis==	9573
replace lm_region = 	9564000	if ao_kreis==	9574
replace lm_region = 	9564000	if ao_kreis==	9575
replace lm_region = 	9564000	if ao_kreis==	9576
replace lm_region = 	9564000	if ao_kreis==	9577
replace lm_region = 	6412000	if ao_kreis==	9661
replace lm_region = 	9662000	if ao_kreis==	9662
replace lm_region = 	9663000	if ao_kreis==	9663
replace lm_region = 	6412000	if ao_kreis==	9671
replace lm_region = 	9662000	if ao_kreis==	9672
replace lm_region = 	9662000	if ao_kreis==	9673
replace lm_region = 	9662000	if ao_kreis==	9674
replace lm_region = 	9663000	if ao_kreis==	9675
replace lm_region = 	6412000	if ao_kreis==	9676
replace lm_region = 	9663000	if ao_kreis==	9677
replace lm_region = 	9662000	if ao_kreis==	9678
replace lm_region = 	9663000	if ao_kreis==	9679
replace lm_region = 	9162000	if ao_kreis==	9761
replace lm_region = 	9162000	if ao_kreis==	9762
replace lm_region = 	9162000	if ao_kreis==	9763
replace lm_region = 	8421000	if ao_kreis==	9764
replace lm_region = 	9162000	if ao_kreis==	9771
replace lm_region = 	9162000	if ao_kreis==	9772
replace lm_region = 	9162000	if ao_kreis==	9773
replace lm_region = 	9162000	if ao_kreis==	9774
replace lm_region = 	8421000	if ao_kreis==	9775
replace lm_region = 	8436064	if ao_kreis==	9776
replace lm_region = 	9162000	if ao_kreis==	9777
replace lm_region = 	9162000	if ao_kreis==	9778
replace lm_region = 	9162000	if ao_kreis==	9779
replace lm_region = 	9162000	if ao_kreis==	9780
replace lm_region = 	10041100	if ao_kreis==	10041
replace lm_region = 	10041100	if ao_kreis==	10042
replace lm_region = 	10041100	if ao_kreis==	10043
replace lm_region = 	10041100	if ao_kreis==	10044
replace lm_region = 	10041100	if ao_kreis==	10045
replace lm_region = 	10041100	if ao_kreis==	10046
replace lm_region = 	11000000	if ao_kreis==	11000
replace lm_region = 	11000000	if ao_kreis==	11100
replace lm_region = 	11000000	if ao_kreis==	12051
replace lm_region = 	14612000	if ao_kreis==	12052
replace lm_region = 	11000000	if ao_kreis==	12053
replace lm_region = 	11000000	if ao_kreis==	12054
replace lm_region = 	11000000	if ao_kreis==	12060
replace lm_region = 	11000000	if ao_kreis==	12061
replace lm_region = 	14612000	if ao_kreis==	12062
replace lm_region = 	11000000	if ao_kreis==	12063
replace lm_region = 	11000000	if ao_kreis==	12064
replace lm_region = 	11000000	if ao_kreis==	12065
replace lm_region = 	14612000	if ao_kreis==	12066
replace lm_region = 	11000000	if ao_kreis==	12067
replace lm_region = 	11000000	if ao_kreis==	12068
replace lm_region = 	11000000	if ao_kreis==	12069
replace lm_region = 	11000000	if ao_kreis==	12070
replace lm_region = 	14612000	if ao_kreis==	12071
replace lm_region = 	11000000	if ao_kreis==	12072
replace lm_region = 	11000000	if ao_kreis==	12073
replace lm_region = 	13001000	if ao_kreis==	13001
replace lm_region = 	13002000	if ao_kreis==	13002
replace lm_region = 	13003000	if ao_kreis==	13003
replace lm_region = 	2000000	if ao_kreis==	13004
replace lm_region = 	13001000	if ao_kreis==	13005
replace lm_region = 	2000000	if ao_kreis==	13006
replace lm_region = 	13003000	if ao_kreis==	13051
replace lm_region = 	13002000	if ao_kreis==	13052
replace lm_region = 	13003000	if ao_kreis==	13053
replace lm_region = 	2000000	if ao_kreis==	13054
replace lm_region = 	13002000	if ao_kreis==	13055
replace lm_region = 	13002000	if ao_kreis==	13056
replace lm_region = 	13003000	if ao_kreis==	13057
replace lm_region = 	2000000	if ao_kreis==	13058
replace lm_region = 	13001000	if ao_kreis==	13059
replace lm_region = 	13001000	if ao_kreis==	13071
replace lm_region = 	13001000	if ao_kreis==	13072
replace lm_region = 	13001000	if ao_kreis==	13073
replace lm_region = 	13001000	if ao_kreis==	13074
replace lm_region = 	2000000	if ao_kreis==	13060
replace lm_region = 	13001000	if ao_kreis==	13061
replace lm_region = 	13002000	if ao_kreis==	13062
replace lm_region = 	13002000	if ao_kreis==	13075
replace lm_region = 	13002000	if ao_kreis==	13076
replace lm_region = 	14511000	if ao_kreis==	14511
replace lm_region = 	14511000	if ao_kreis==	14521
replace lm_region = 	14511000	if ao_kreis==	14522
replace lm_region = 	14511000	if ao_kreis==	14523
replace lm_region = 	14511000	if ao_kreis==	14524
replace lm_region = 	14612000	if ao_kreis==	14612
replace lm_region = 	14612000	if ao_kreis==	14625
replace lm_region = 	14612000	if ao_kreis==	14626
replace lm_region = 	14612000	if ao_kreis==	14627
replace lm_region = 	14612000	if ao_kreis==	14628
replace lm_region = 	14713000	if ao_kreis==	14713
replace lm_region = 	14713000	if ao_kreis==	14729
replace lm_region = 	14713000	if ao_kreis==	14730
replace lm_region = 	14713000	if ao_kreis==	15001
replace lm_region = 	14713000	if ao_kreis==	15002
replace lm_region = 	15003000	if ao_kreis==	15003
replace lm_region = 	2000000	if ao_kreis==	15081
replace lm_region = 	14713000	if ao_kreis==	15082
replace lm_region = 	15003000	if ao_kreis==	15083
replace lm_region = 	14713000	if ao_kreis==	15084
replace lm_region = 	15085370	if ao_kreis==	15085
replace lm_region = 	15003000	if ao_kreis==	15086
replace lm_region = 	14713000	if ao_kreis==	15087
replace lm_region = 	14713000	if ao_kreis==	15088
replace lm_region = 	15003000	if ao_kreis==	15089
replace lm_region = 	15003000	if ao_kreis==	15090
replace lm_region = 	14713000	if ao_kreis==	15091
replace lm_region = 	16051000	if ao_kreis==	16051
replace lm_region = 	16051000	if ao_kreis==	16052
replace lm_region = 	16051000	if ao_kreis==	16053
replace lm_region = 	16054000	if ao_kreis==	16054
replace lm_region = 	16051000	if ao_kreis==	16055
replace lm_region = 	16051000	if ao_kreis==	16056
replace lm_region = 	3152012		if ao_kreis==	16061
replace lm_region = 	3152012		if ao_kreis==	16062
replace lm_region = 	16051000	if ao_kreis==	16063
replace lm_region = 	16051000	if ao_kreis==	16064
replace lm_region = 	16051000	if ao_kreis==	16065
replace lm_region = 	16054000	if ao_kreis==	16066
replace lm_region = 	16051000	if ao_kreis==	16067
replace lm_region = 	16051000	if ao_kreis==	16068
replace lm_region = 	16054000	if ao_kreis==	16069
replace lm_region = 	16051000	if ao_kreis==	16070
replace lm_region = 	16051000	if ao_kreis==	16071
replace lm_region = 	9463000		if ao_kreis==	16072
replace lm_region = 	16051000	if ao_kreis==	16073
replace lm_region = 	16051000	if ao_kreis==	16074
replace lm_region = 	16051000	if ao_kreis==	16075
replace lm_region = 	16051000	if ao_kreis==	16076
replace lm_region = 	14713000	if ao_kreis==	16077


// Change identifies to # 1-50
replace lm_region =  	1	if lm_region==	2000000
replace lm_region =  	2	if lm_region==	3101000
replace lm_region =  	3	if lm_region==	3152012
replace lm_region =  	4	if lm_region==	3241001
replace lm_region =  	5	if lm_region==	4011000
replace lm_region =  	6	if lm_region==	5711000
replace lm_region =  	7	if lm_region==	3403000
replace lm_region =  	8	if lm_region==	3404000
replace lm_region =  	9	if lm_region==	5515000
replace lm_region =  	10	if lm_region==	5113000
replace lm_region =  	11	if lm_region==	5313000
replace lm_region =  	12	if lm_region==	5315000
replace lm_region =  	13	if lm_region==	5970040
replace lm_region =  	14	if lm_region==	6412000
replace lm_region =  	15	if lm_region==	8222000
replace lm_region =  	16	if lm_region==	6611000
replace lm_region =  	17	if lm_region==	7111000
replace lm_region =  	18	if lm_region==	7211000
replace lm_region =  	19	if lm_region==	10041100
replace lm_region =  	20	if lm_region==	8212000
replace lm_region =  	21	if lm_region==	8111000
replace lm_region =  	22	if lm_region==	8311000
replace lm_region =  	23	if lm_region==	8317096
replace lm_region =  	24	if lm_region==	8326074
replace lm_region =  	25	if lm_region==	8335075
replace lm_region =  	26	if lm_region==	8336050
replace lm_region =  	27	if lm_region==	8421000
replace lm_region =  	28	if lm_region==	8436064
replace lm_region =  	29	if lm_region==	9162000
replace lm_region =  	30	if lm_region==	9262000
replace lm_region =  	31	if lm_region==	9362000
replace lm_region =  	32	if lm_region==	9564000
replace lm_region =  	33	if lm_region==	9363000
replace lm_region =  	34	if lm_region==	9479136
replace lm_region =  	35	if lm_region==	9462000
replace lm_region =  	36	if lm_region==	9463000
replace lm_region =  	37	if lm_region==	9464000
replace lm_region =  	38	if lm_region==	9662000
replace lm_region =  	39	if lm_region==	9663000
replace lm_region =  	40	if lm_region==	11000000
replace lm_region =  	41	if lm_region==	14612000
replace lm_region =  	42	if lm_region==	13001000
replace lm_region =  	43	if lm_region==	13002000
replace lm_region =  	44	if lm_region==	13003000
replace lm_region =  	45	if lm_region==	14511000
replace lm_region =  	46	if lm_region==	14713000
replace lm_region =  	47	if lm_region==	15003000
replace lm_region =  	48	if lm_region==	15085370
replace lm_region =  	49	if lm_region==	16051000
replace lm_region =  	50	if lm_region==	16054000
			
	
// Label values
label define lm_region_labels 	1  "Hamburg"
label define lm_region_labels 	2  "Braunschweig", modify
label define lm_region_labels 	3  "Göttingen", modify
label define lm_region_labels 	4  "Hannover", modify
label define lm_region_labels 	5  "Bremen", modify
label define lm_region_labels 	6  "Bielefeld", modify
label define lm_region_labels 	7  "Oldenburg (Oldenburg)", modify
label define lm_region_labels 	8  "Osnabrück", modify
label define lm_region_labels 	9  "Münster", modify
label define lm_region_labels 	10  "Essen", modify
label define lm_region_labels 	11  "Aachen", modify
label define lm_region_labels 	12  "Köln", modify
label define lm_region_labels 	13  "Siegen", modify
label define lm_region_labels 	14  "Frankfurt am Main", modify
label define lm_region_labels 	15  "Mannheim", modify
label define lm_region_labels 	16  "Kassel", modify
label define lm_region_labels 	17  "Koblenz", modify
label define lm_region_labels 	18  "Trier", modify
label define lm_region_labels 	19  "Saarbrücken", modify
label define lm_region_labels 	20  "Karlsruhe", modify
label define lm_region_labels 	21  "Stuttgart", modify
label define lm_region_labels 	22  "Freiburg im Breisgau", modify
label define lm_region_labels 	23  "Offenburg", modify
label define lm_region_labels 	24  "Villingen-Schwenningen", modify
label define lm_region_labels 	25  "Singen (Hohentwiel)", modify
label define lm_region_labels 	26  "Lörrach", modify
label define lm_region_labels 	27  "Ulm", modify
label define lm_region_labels 	28  "Ravensburg", modify
label define lm_region_labels 	29  "München", modify
label define lm_region_labels 	30  "Passau", modify
label define lm_region_labels 	31  "Regensburg", modify
label define lm_region_labels 	32  "Nürnberg", modify
label define lm_region_labels 	33  "Weiden i.d. OPf.", modify
label define lm_region_labels 	34  "Marktredwitz", modify
label define lm_region_labels 	35  "Bayreuth", modify
label define lm_region_labels 	36  "Coburg", modify
label define lm_region_labels 	37  "Hof", modify
label define lm_region_labels 	38  "Schweinfurt", modify
label define lm_region_labels 	39  "Würzburg", modify
label define lm_region_labels 	40  "Berlin", modify
label define lm_region_labels 	41  "Dresden", modify
label define lm_region_labels 	42  "Greifswald", modify
label define lm_region_labels 	43  "Neubrandenburg", modify
label define lm_region_labels 	44  "Rostock", modify
label define lm_region_labels 	45  "Chemnitz", modify
label define lm_region_labels 	46  "Leipzig", modify
label define lm_region_labels 	47  "Magdeburg", modify
label define lm_region_labels 	48  "Wernigerode", modify
label define lm_region_labels 	49  "Erfurt", modify
label define lm_region_labels 	50  "Suhl", modify	

label values lm_region lm_region_labels


}
tab lm_region, m // checking

if "`c(username)'"=="predoc" {
	replace lm_region = floor(runiform(0,17))
}


* regression 1
di "regression 2019"
reg ln_wage i.female i.occupation i.lm_region i.educ##(c.age c.age2 c.age3) if jahr==2019
predict p_ln_wage19, xb // predicted value from regression

gen ln_wage19 = ln_wage if jahr==2019 & female!=. & occupation!=. & lm_region!=. & educ!=. & age!=.

* regression 2017
di "regression 2017"
reg ln_wage i.female i.occupation i.lm_region i.educ##(c.age c.age2 c.age3) if jahr==2017
predict p_ln_wage17, xb // predicted value from regression

gen ln_wage17 = ln_wage if jahr==2017 & female!=. & occupation!=. & lm_region!=. & educ!=. & age!=.


* cell-level regressions

gen numobs19 = 1 if jahr==2019
gen numobs17 = 1 if jahr==2017

su age
local minval = r(min)
local maxval = r(max)

* generating education times age dummies
forvalues n = 1/3 {
	forvalues m = `minval'/`maxval' {
		gen aed`n'_`m' = educ==`n' & age==`m'
	}
}

keep if ln_wage!=. & female!=. & occupation!=. & lm_region!=. & educ!=. & age!=.

collapse (mean) p_ln_wage19 ln_wage19 p_ln_wage17 ln_wage17 (sum) numobs19 numobs17, by(female occupation lm_region aed*)

save "$data/cells_collapsed.dta", replace

di "weighted regression 2019"
reg ln_wage19 p_ln_wage19 [aweight=numobs19], robust

di "unweighted regression 2019 for comparison"
reg ln_wage19 p_ln_wage19, robust

di "2017 on 2019 weighted regression"
reg ln_wage19 p_ln_wage17 [aweight = numobs17], robust

di "unweighted regression 2017 on 2019 for comparison"
reg ln_wage19 p_ln_wage17, robust




log close
